/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package repository;

import java.util.*;
import java.sql.*;
import dto.*;



public class UserRepository {
    
    
    private DataBase db=null;
    private PreparedStatement sqlGetById;
    private PreparedStatement sqlGetAll;
    private PreparedStatement sqlGetByLogin;
    
    public UserRepository()
    {
        try
        {
            db=new DataBase();
        }
        catch(Exception e)
        {}
    }
    
    public User getById(int id)
    {
        try
        {
            sqlGetById=db.connection.prepareStatement("SELECT id, userName, surname, login, password, role FROM Users WHERE id = ? ");
            sqlGetById.setInt(1, id);
            ResultSet resultSet=sqlGetById.executeQuery();
            if(!resultSet.next())
            {
                return null;
            }
            int gotId=resultSet.getInt(1);
            String userName=resultSet.getString(2);
            String surname=resultSet.getString(3);
            String login=resultSet.getString(4);
            String password=resultSet.getString(5);
            String role=resultSet.getString(6);  
            User user=new User(gotId,userName,surname,login,password,role);
            return user;
        }
        catch(Exception e)
        {
            return null;
        }
    }
    
    public List<User> getAll()
    {
        try
        {
            sqlGetAll=db.connection.prepareStatement("SELECT id, userName, surname, login, password, role FROM Products");
            ResultSet resultSet=sqlGetAll.executeQuery();
            List<User> pList=new ArrayList<User>();
            while (resultSet.next())
            {       
                int gotId=resultSet.getInt(1);
                String userName=resultSet.getString(2);
                String surname=resultSet.getString(3);
                String login=resultSet.getString(4);
                String password=resultSet.getString(5);
                String role=resultSet.getString(6);  
                User user=new User(gotId,userName,surname,login,password,role);
                pList.add(user);
            }
            return pList;
        }
        catch(Exception e)
        {
            return null;
        }        
    }
    
    
    public User getByLogin(String login)
    {
        try
        {
            sqlGetById=db.connection.prepareStatement("SELECT id, userName, surname, login, password, role FROM Users WHERE login = ? ");
            sqlGetById.setString(1, login);
            ResultSet resultSet=sqlGetById.executeQuery();
            if(!resultSet.next())
            {
                return null;
            }
            int gotId=resultSet.getInt(1);
            String userName=resultSet.getString(2);
            String surname=resultSet.getString(3);
            String gotLogin=resultSet.getString(4);
            String password=resultSet.getString(5);
            String role=resultSet.getString(6);  
            User user=new User(gotId,userName,surname,gotLogin,password,role);
            return user;
        }
        catch(Exception e)
        {
            return null;
        }
    }
    
}
